IF EXISTS ( SELECT  1
            FROM    sysobjects
            WHERE   id = OBJECT_ID('p_QueryInventory') ) 
    DROP PROC p_QueryInventory

GO

CREATE PROC p_QueryInventory
    @stocknum VARCHAR(50) ,
    @icitemnum VARCHAR(200)
AS 
    BEGIN
	
        DECLARE @cyear INT ,
            @cperiod INT ,
            @stockid INT ,
            @icitemid INT
        SELECT  @cyear = FValue
        FROM    t_SystemProfile AS tsp
        WHERE   FCategory = 'IC'
                AND FKey = 'CurrentYear'
        SELECT  @cperiod = FValue
        FROM    t_SystemProfile AS tsp
        WHERE   FCategory = 'IC'
                AND FKey = 'CurrentPeriod'
	
        IF @stocknum IS NOT NULL 
            SELECT  @stockid = FItemID
            FROM    t_stock
            WHERE   fnumber = @stocknum
        IF @icitemnum IS NOT	NULL 
            SELECT  @icitemid = FitemID
            FROM    t_ICItemCore AS tiic
            WHERE   FNumber = @icitemnum
	
        SELECT  FStockID ,
                FItemID ,
                FBegQty AS FQty
        INTO    #beg
        FROM    ICInvBal AS iib
        WHERE   FYear = @cyear
                AND FPeriod = @cperiod
                AND ( FStockID = @stockid
                      OR @stockid IS NULL
                    )
                AND ( FItemID = @icitemid
                      OR @icitemid IS NULL
                    )
	
        SELECT  ( SELECT    fnumber
                  FROM      t_stock
                  WHERE     FItemID = b.FStockID
                ) StockNumber ,
                tiic.FNumber ICItemNumber ,
                CAST(ISNULL(b.FQty, 0) AS DECIMAL(28,6)) AS Qty ,
                CAST(0 AS DECIMAL(28,6)) AS WaitingSendQty ,
                tmu.FName Unit
        FROM    t_ICItem AS tiic
                INNER JOIN t_MeasureUnit AS tmu ON tiic.FUnitID = tmu.FItemID
                LEFT JOIN #beg b ON b.FItemID = tiic.FItemID
        WHERE   b.FQty <> 0

    END
   
   GO
EXEC p_QueryInventory '', ''